package com.duleme.dao;

import com.duleme.common.Config;
import com.duleme.common.JsonUtils;
import com.duleme.entity.Book;
import com.google.gson.Gson;
import com.duleme.common.DBUtils;
import com.duleme.entity.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class UserDao {
    //插入新用户
    public boolean insert(User user) {
        String sql = "INSERT INTO user (UserAccount,UserName,UserPassword,identity,createDate,avatar) VALUES(?,?,?,?,?,?)";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1,user.getUserAccount());
            ps.setString(2, user.getUserName());
            ps.setString(3, user.getUserPwd());
            ps.setInt(4,user.getIdentity());
            //数据类型转换，将java.util.Date 转为java.sql.Date
            ps.setDate(5, new java.sql.Date(user.getCreateDate().getTime()));
            ps.setString(6, Config.USER_DEFAULT_AVATAR_ACCESS_PATH);
            System.out.println(sql);
            if (ps.executeUpdate() > 0) {
                DBUtils.close(connection, ps, null);
                return true;
            }
            DBUtils.close(connection,ps,null);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    //检查注册时用户的账号（手机号）是否重复
    public boolean checkAccountValid(String account){
        String sql="SELECT * FROM USER WHERE userAccount=?";
        try{
            Connection connection=DBUtils.getConnection();
            PreparedStatement ps= connection.prepareStatement(sql);
            ps.setString(1,account);
            ResultSet rs=ps.executeQuery();
            if(rs.next()){
                System.out.println(rs.getString("UserName"));
                DBUtils.close(connection,ps,rs);
                return false;
            }
            DBUtils.close(connection,ps,rs);
            return true;
        }catch (Exception e){
            e.getStackTrace();
            return false;
        }
    }
    //根据账号密码查询用户
    public User findUserByAccAndPwd(String account,String pwd){
        String sql="SELECT * FROM user WHERE userAccount=? AND userPassword=?";
        try{
            Connection connection=DBUtils.getConnection();
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.setString(1,account);
            ps.setString(2,pwd);
            ResultSet rs=ps.executeQuery();
            User user=new User();
            if(rs.next()){
                user.setUserAccount(rs.getString("UserAccount"));
                user.setUserName(rs.getString("UserName"));
                //user.setUserPwd(rs.getString("UserPassword")); //密码暂时不存储
                user.setUserId(rs.getInt("UserId"));

                user.setIdentity(rs.getInt("identity"));
                user.setAvatar(rs.getString("avatar"));
                user.setCreateDate(rs.getDate("createDate"));
                DBUtils.close(connection,ps,rs);
                return user;
            }
        }catch (Exception e){
            e.getStackTrace();
            return null;
        }
        return null;
    }
    //根据id查询用户
    public User findById(int id){
        String sql="SELECT * FROM user WHERE userId=?";
        try{
            Connection connection= DBUtils.getConnection();
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.setInt(1,id);
            ResultSet rs=ps.executeQuery();
            if(rs.next()){
                User user=new User();
                user.setUserId(rs.getInt("userId"));
                user.setCreateDate(rs.getDate("createDate"));
                user.setAvatar(rs.getString("avatar"));
                user.setIdentity(rs.getInt("identity"));
                user.setUserName(rs.getString("userName"));
                user.setUserAccount(rs.getString("userAccount"));
                user.setUserPwd(rs.getString("userPassword"));

                return user;
            }
            DBUtils.close(connection,ps,rs);
            return  null;
        }catch (Exception e){
            e.getStackTrace();
            return null;
        }
    }

    //查询所有用户
    public List<User> findAllUsers(){
        String sql="SELECT * FROM user";
        try {
            List<User> users=new ArrayList<>();
            Connection connection=DBUtils.getConnection();
            PreparedStatement ps=connection.prepareStatement(sql);
            ResultSet rs=ps.executeQuery();
            while (rs.next()){
                User user=new User();
                user.setUserId(rs.getInt("userId"));
                user.setCreateDate(rs.getDate("createDate"));
                user.setAvatar(rs.getString("avatar"));
                user.setIdentity(rs.getInt("identity"));
                user.setUserName(rs.getString("userName"));
                user.setUserAccount(rs.getString("userAccount"));
                user.setUserPwd(rs.getString("userPassword"));

                users.add(user);
            }
            DBUtils.close(connection,ps,rs);
            return users;
        }catch (Exception e){
            e.getStackTrace();
            return null;
        }
    }
    //删除一个用户根据id
    public boolean deleteUserById(int id){
        String sql="DELETE FROM user WHERE userId=?";
        try {
            Connection connection=DBUtils.getConnection();
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.setInt(1,id);
            if(ps.executeUpdate()>0){
                DBUtils.close(connection,ps,null);
                return true;
            }
            DBUtils.close(connection,ps,null);
            return false;
        }catch (Exception e){
            e.getStackTrace();
            return false;
        }
    }
    //完整版本，同时也会在userInfo表中删除对应项
    public boolean deleteUserByUserId(int userId) throws SQLException{
        String sql1="DELETE FROM user WHERE userId=?";
        String sql2="DELETE FROM userinfo WHERE userId=?";
        Connection connection=DBUtils.getConnection();
        PreparedStatement ps1=connection.prepareStatement(sql1);
        PreparedStatement ps2=connection.prepareStatement(sql2);
        try {

            connection.setAutoCommit(false);
            ps1.setInt(1,userId);
            ps2.setInt(1,userId);
            int result1=ps1.executeUpdate();
            int result2=ps2.executeUpdate();
            connection.commit();
            System.out.println(result1);
            System.out.println(result2);
            return true;

        }catch (Exception e){
            e.printStackTrace();
            connection.rollback();
            return false;
        }finally {
            connection.setAutoCommit(true);
            connection.close();
            ps1.close();
            ps2.close();
        }
    }
    public boolean update(User user){
        String sql="UPDATE user SET userPassword=?,userName=?,avatar=? WHERE userId=?";
        try {
            Connection connection=DBUtils.getConnection();
            PreparedStatement ps=connection.prepareStatement(sql);
            if (user.getUserPwd()!=null){
                ps.setString(1, user.getUserPwd());
            }else ps.setString(1,"userPassword");
            if (user.getUserName()!=null){
                ps.setString(2,user.getUserName());
            }else ps.setString(2,"userName");
            if (user.getAvatar()!=null){
                ps.setString(3,user.getAvatar());
            }else ps.setString(3,"avatar");
            ps.setInt(4,user.getUserId());
            if (ps.executeUpdate()>0){
                DBUtils.close(connection,ps,null);
                return true;
            }
            DBUtils.close(connection,ps,null);
            return false;
        }catch (Exception e){
            e.getStackTrace();
            return false;
        }
    }
    public boolean updateAvatar(String avatar,int userId){
        String sql="UPDATE user SET avatar=? WHERE userId=?";
        try {
            Connection connection=DBUtils.getConnection();
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.setString(1,avatar);
            ps.setInt(2,userId);
            if(ps.executeUpdate()>0){
                DBUtils.close(connection,ps,null);
                return true;
            }
            DBUtils.close(connection,ps,null);
            return false;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
    }
    public static void main(String[] args){
//        User user=new User();
//        user.setUserAccount("757902783");
//        user.setUserName("stupidhead");
//        user.setUserPwd("erqwarq");
//        user.setIdentity(0);
//        user.setCreateDate(new Date());
//        UserDao dao=new UserDao();
//        dao.insert(user);
//        String account="757902783";
//        String pwd="123qwer";
//        UserDao dao=new UserDao();
//        User user=dao.findUserByAccAndPwd(account,pwd);
//        if(user==null)System.out.println("未找到！");
//        else
//        System.out.print(user.getUserId());
//        String account="757902783";
//        UserDao dao=new UserDao();
//        //boolean b=dao.checkAccountValid(account);
//        System.out.println(dao.findById(100002).getUserName());
//        Map<String,String> map=new HashMap<>();
//        map.put("info","error");
//        Gson gson=new Gson();
//        System.out.println(gson.toJson(map));
//        User user =new User();
//        user.setUserId(100001);
//        user.setUserName("小雨萌");
//        UserDao dao=new UserDao();
//        System.out.println(dao.update(user));
        //System.out.println(user.getUserId());
//        Map<String,Integer> map=new HashMap<String,Integer>(){
//            {
//                put("result",1);
//            }
//        };
//        Map<String,Integer> map2=new HashMap<String,Integer>();
//        map2.put("result",1);
//        System.out.print(JsonUtils.toJson(map2));
    UserDao dao=new UserDao();
    try {
        dao.deleteUserByUserId(100003);
    }catch (SQLException e){
        e.printStackTrace();
    }

    }

    public Boolean alterPassword(String userAccount, String password) {
        String sql = "UPDATE user SET userPassword=? WHERE userAccount=?";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1,password);
            statement.setString(2,userAccount);
            if (statement.executeUpdate()>0){
                DBUtils.close(connection,statement,null);
                return true;
            }
            return false;
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }
}
